<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Categories
 *
 * @author Administrator
 */
require_once 'Categories.php';
require_once 'Users.php';

class Categories {

    //put your code here
    private $ID;
    private $_parent;
    private $_alias;
    private $_title;
    private $_level;
    private $_order;
    private $_state;
    private $_description;
    private $_componentID;
    private $_createdUserID;
    private $_modifiedUserID;
    private $_createdDate;
    private $_modifiedDate;
    private $_image;
    // các đối tượng
    private $_createUser;
    private $_modifiedUser;
    private $_component;

    public function getImage() {
        return $this->_image;
    }

    public function setImage($image) {
        $this->_image = $image;
    }

    public function getID() {
        return $this->ID;
    }

    public function setID($ID) {
        $this->ID = $ID;
    }

    public function getParent() {
        return $this->_parent;
    }

    public function setParent($parent) {
        $this->_parent = $parent;
    }

    public function getAlias() {
        return $this->_alias;
    }

    public function setAlias($alias) {
        $this->_alias = $alias;
    }

    public function getTitle() {
        return $this->_title;
    }

    public function setTitle($title) {
        $this->_title = $title;
    }

    public function getLevel() {
        return $this->_level;
    }

    public function setLevel($level) {
        $this->_level = $level;
    }

    public function getOrder() {
        return $this->_order;
    }

    public function setOrder($order) {
        $this->_order = $order;
    }

    public function getState() {
        return $this->_state;
    }

    public function setState($state) {
        $this->_state = $state;
    }

    public function getDescription() {
        return $this->_description;
    }

    public function setDescription($description) {
        $this->_description = $description;
    }

    public function getComponentID() {
        return $this->_componentID;
    }

    public function setComponentID($componentID) {
        $this->_componentID = $componentID;
    }

    public function getCreatedUserID() {
        return $this->_createdUserID;
    }

    public function setCreatedUserID($createdUserID) {
        $this->_createdUserID = $createdUserID;
    }

    public function getModifiedUserID() {
        return $this->_modifiedUserID;
    }

    public function setModifiedUserID($modifiedUserID) {
        $this->_modifiedUserID = $modifiedUserID;
    }

    public function getCreatedDate() {
        return $this->_createdDate;
    }

    public function setCreatedDate($createdDate) {
        $this->_createdDate = $createdDate;
    }

    public function getModifiedDate() {
        return $this->_modifiedDate;
    }

    public function setModifiedDate($modifiedDate) {
        $this->_modifiedDate = $modifiedDate;
    }

    public function getCreateUser() {
        return $this->_createUser;
    }

    public function setCreateUser($createUser) {
        $this->_createUser = $createUser;
    }

    public function getModifiedUser() {
        return $this->_modifiedUser;
    }

    public function setModifiedUser($modifiedUser) {
        $this->_modifiedUser = $modifiedUser;
    }

    public function getComponent() {
        return $this->_component;
    }

    public function setComponent($component) {
        $this->_component = $component;
    }

    /**
     * tải danh sách nhóm theo mã cha
     * @param type $db
     * 
     */
    public function getListByParentIDAndComponentID($db) {
        if ($db != null) {
            $sql = "SELECT
                        `C_ID`,
                        `C_Parent`,
                        `C_Alias`,
                        `C_Title`,
                        `C_Level`,
                        `C_Order`,
                        `C_State`,
                        `C_Description`,
                        `C_ComponentID`,
                        `C_CreatedUser`,
                        `C_ModifiedUser`,
                        Date_Format(`C_CreatedDate`, '%d/%m/%Y') AS `C_CreatedDate`,      
                        Date_Format(`C_ModifiedDate`, '%d/%m/%Y') AS `C_ModifiedDate`,
                        C_Image
                      FROM `categories`
                      WHERE C_Parent = :C_Parent
                      AND C_ComponentID = :C_ComponentID
                      ORDER BY C_Order DESC;";

            $parameters = array(':C_Parent', ':C_ComponentID');
            $values = array($this->getParent(), $this->getComponentID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $category = new Categories();
                    $category->setID($row->C_ID);
                    $category->setParent($row->C_Parent);
                    $category->setAlias($row->C_Alias);
                    $category->setTitle($row->C_Title);
                    $category->setLevel($row->C_Level);
                    $category->setOrder($row->C_Order);
                    $category->setState($row->C_State);
                    $category->setDescription($row->C_Description);
                    $category->setComponentID($row->C_ComponentID);
                    $category->setCreatedUserID($row->C_CreatedUser);
                    $category->setModifiedUserID($row->C_ModifiedUser);
                    $category->setCreatedDate($row->C_CreatedDate);
                    $category->setModifiedDate($row->C_ModifiedDate);
                    $category->setImage($row->C_Image);

                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($category->getCreatedUserID());
                    $createdUser->getByID($db);
                    $category->setCreateUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($category->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $category->setModifiedUser($modifiedUser);



                    $rows[] = $category;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    public function getListByComponentID($db) {
        if ($db != null) {
            $sql = "SELECT
                        `C_ID`,
                        `C_Parent`,
                        `C_Alias`,
                        `C_Title`,
                        `C_Level`,
                        `C_Order`,
                        `C_State`,
                        `C_Description`,
                        `C_ComponentID`,
                        `C_CreatedUser`,
                        `C_ModifiedUser`,
                        `C_CreatedDate`,      
                        `C_ModifiedDate`,
                        C_Image
                      FROM `categories`
                      WHERE C_ComponentID = :C_ComponentID
                      ORDER BY C_Order DESC;";

            $parameters = array(':C_ComponentID');
            $values = array($this->getComponentID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $category = new Categories();
                    $category->setID($row->C_ID);
                    $category->setParent($row->C_Parent);
                    $category->setAlias($row->C_Alias);
                    $category->setTitle($row->C_Title);
                    $category->setLevel($row->C_Level);
                    $category->setOrder($row->C_Order);
                    $category->setState($row->C_State);
                    $category->setDescription($row->C_Description);
                    $category->setComponentID($row->C_ComponentID);
                    $category->setCreatedUserID($row->C_CreatedUser);
                    $category->setModifiedUserID($row->C_ModifiedUser);
                    $category->setCreatedDate($row->C_CreatedDate);
                    $category->setModifiedDate($row->C_ModifiedDate);
                    $category->setImage($row->C_Image);

                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($category->getCreatedUserID());
                    $createdUser->getByID($db);
                    $category->setCreateUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($category->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $category->setModifiedUser($modifiedUser);



                    $rows[] = $category;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    public function getByID($db) {
        if ($db != null) {
            $sql = "SELECT
                        `C_ID`,
                        `C_Parent`,
                        `C_Alias`,
                        `C_Title`,
                        `C_Level`,
                        `C_Order`,
                        `C_State`,
                        `C_Description`,
                        `C_ComponentID`,
                        `C_CreatedUser`,
                        `C_ModifiedUser`,
                        Date_Format(`C_CreatedDate`, '%d/%m/%Y') AS `C_CreatedDate`,      
                        Date_Format(`C_ModifiedDate`, '%d/%m/%Y') AS `C_ModifiedDate`,
                        C_Image
                      FROM `categories`
                      WHERE C_ID = :C_ID;";

            $parameters = array(':C_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {
                $row = $result->fetch();

                $this->setID($row->C_ID);
                $this->setParent($row->C_Parent);
                $this->setAlias($row->C_Alias);
                $this->setTitle($row->C_Title);
                $this->setLevel($row->C_Level);
                $this->setOrder($row->C_Order);
                $this->setState($row->C_State);
                $this->setDescription($row->C_Description);
                $this->setComponentID($row->C_ComponentID);
                $this->setCreatedUserID($row->C_CreatedUser);
                $this->setModifiedUserID($row->C_ModifiedUser);
                $this->setCreatedDate($row->C_CreatedDate);
                $this->setModifiedDate($row->C_ModifiedDate);
                $this->setImage($row->C_Image);

                // created user
                $createdUser = new Users();
                $createdUser->setID($this->getCreatedUserID());
                $createdUser->getByID($db);
                $this->setCreateUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($this->getModifiedUserID());
                $modifiedUser->getByID($db);
                $this->setModifiedUser($modifiedUser);

                return $this;
            } else {
                return null;
            }
        }
    }

    public function getByAlias($db) {
        if ($db != null) {
            $sql = "SELECT
                        `C_ID`,
                        `C_Parent`,
                        `C_Alias`,
                        `C_Title`,
                        `C_Level`,
                        `C_Order`,
                        `C_State`,
                        `C_Description`,
                        `C_ComponentID`,
                        `C_CreatedUser`,
                        `C_ModifiedUser`,
                        Date_Format(`C_CreatedDate`, '%d/%m/%Y') AS `C_CreatedDate`,      
                        Date_Format(`C_ModifiedDate`, '%d/%m/%Y') AS `C_ModifiedDate`,
                        C_Image
                      FROM `categories`
                      WHERE C_Alias = :C_Alias;";

            $parameters = array(':C_Alias');
            $values = array($this->getAlias());
            $types = array(PDO::PARAM_STR);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {
                $row = $result->fetch();

                $this->setID($row->C_ID);
                $this->setParent($row->C_Parent);
                $this->setAlias($row->C_Alias);
                $this->setTitle($row->C_Title);
                $this->setLevel($row->C_Level);
                $this->setOrder($row->C_Order);
                $this->setState($row->C_State);
                $this->setDescription($row->C_Description);
                $this->setComponentID($row->C_ComponentID);
                $this->setCreatedUserID($row->C_CreatedUser);
                $this->setModifiedUserID($row->C_ModifiedUser);
                $this->setCreatedDate($row->C_CreatedDate);
                $this->setModifiedDate($row->C_ModifiedDate);
                $this->setImage($row->C_Image);

                // created user
                $createdUser = new Users();
                $createdUser->setID($this->getCreatedUserID());
                $createdUser->getByID($db);
                $this->setCreateUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($this->getModifiedUserID());
                $modifiedUser->getByID($db);
                $this->setModifiedUser($modifiedUser);

                return $this;
            } else {
                return null;
            }
        }
    }

    // them moi category
    public function add($db) {
        if ($db != null) {
            $sql = "INSERT INTO `categories`
                                (
                                 `C_Parent`,
                                 `C_Alias`,
                                 `C_Title`,
                                 `C_Level`,
                                 `C_Order`,
                                 `C_State`,
                                 `C_Description`,
                                 `C_ComponentID`,
                                 `C_CreatedUser`,
                                 `C_ModifiedUser`,
                                 `C_CreatedDate`,
                                 `C_ModifiedDate`,
                                 `C_Image`)
                    VALUES (
                            :C_Parent,
                            :C_Alias,
                            :C_Title,
                            :C_Level,
                            :C_Order,
                            :C_State,
                            :C_Description,
                            :C_ComponentID,
                            :C_CreatedUser,
                            :C_ModifiedUser,
                            :C_CreatedDate,
                            :C_ModifiedDate,
                            :C_Image);";

            $parameters = array(
                ':C_Parent', ':C_Alias',
                ':C_Title', ':C_Level',
                ':C_Order', ':C_State',
                ':C_Description', ':C_ComponentID',
                ':C_CreatedUser', ':C_ModifiedUser',
                ':C_CreatedDate', ':C_ModifiedDate',
                ':C_Image'
            );
            $values = array(
                $this->getParent(), $this->getAlias(),
                $this->getTitle(), $this->getLevel(),
                $this->getOrder(), $this->getState(),
                $this->getDescription(), $this->getComponentID(),
                $this->getCreatedUserID(), $this->getModifiedUserID(),
                $this->getCreatedDate(), $this->getModifiedDate(),
                $this->getImage()
            );
            $types = array(
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // cap nhat category
    public function delete($db) {
        if ($db != null) {
            $sql = "DELETE
                    FROM `categories`
                    WHERE `C_ID` = :C_ID;";
            $parameters = array(':C_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // cap nhat category
    public function update($db) {
        if ($db != null) {
            $sql = "UPDATE `categories`
                    SET 
                      `C_Parent` = :C_Parent,
                      `C_Alias` = :C_Alias,
                      `C_Title` = :C_Title,
                      `C_Level` = :C_Level,
                      `C_State` = :C_State,
                      `C_Description` = :C_Description,                 
                      `C_ModifiedUser` = :C_ModifiedUser,                      
                      `C_ModifiedDate` = :C_ModifiedDate,
                      `C_Image` = :C_Image
                    WHERE `C_ID` = :C_ID;";

            $parameters = array(
                ':C_Parent', ':C_Alias',
                ':C_Title', ':C_Level',
                ':C_State',
                ':C_Description',
                ':C_ModifiedUser',
                ':C_ModifiedDate',
                ':C_Image',
                ':C_ID'
            );
            $values = array(
                $this->getParent(), $this->getAlias(),
                $this->getTitle(), $this->getLevel(),
                $this->getState(),
                $this->getDescription(),
                $this->getModifiedUserID(),
                $this->getModifiedDate(),
                $this->getImage(),
                $this->getID()
            );
            $types = array(
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT,
                PDO::PARAM_STR,
                PDO::PARAM_INT,
                PDO::PARAM_STR,
                PDO::PARAM_STR,
                PDO::PARAM_INT
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // cap nhat category
    public function updateState($db) {
        if ($db != null) {
            $sql = "UPDATE `categories`
                    SET                       
                      `C_State` = :C_State
                    WHERE `C_ID` = :C_ID;";
            $parameters = array(':C_State', ':C_ID');
            $values = array($this->getState(), $this->getID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT,);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // ham lay level
    public function getNextLevelByParentID($db) {
        if ($db != null) {
            // neu co ma parent thi tra ve level parent + 1
            if ($this->getParent() != 0) {
                $sql = 'SELECT
                        `C_Level`
                      FROM `categories`
                      WHERE C_ID = :C_Parent';
                $parameters = array(':C_Parent');
                $values = array($this->getParent());
                $types = array(PDO::PARAM_INT);
                $result = $db->executeQuery($sql, $parameters, $values, $types);
                if ($result != null) {
                    $row = $result->fetch();
                    return $row->C_Level + 1;
                }
            } else {
                // neu không co parent thi tra ve 0
                return 0;
            }
        }
    }

    // hàm lấy thứ tự cao nhất
    public function getMaxOrder($db) {
        if ($db != null) {
            $sql = "SELECT MAX(C_Order) + 1 AS MaxOrder FROM categories";
            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            $row = $result->fetch();
            if ($row->MaxOrder != null) {
                return $row->MaxOrder;
            } else {
                return 1;
            }
        }
    }

    public function orderUp($db) {
        if ($db != null) {
            // câu lệnh tải currentOrder, nextOrder, nextArticleID
            $sql = "SELECT (SELECT `C_Order` 
                    FROM categories 
                    WHERE C_ID = :C_ID) AS `currentOrder`,               
                
                    (SELECT `C_Order` FROM categories WHERE C_Level = :C_Level AND C_ComponentID = :C_ComponentID AND `C_Order` > 
                    (SELECT `C_Order` FROM categories WHERE C_ID = :C_ID) 
                    ORDER BY `C_Order` ASC LIMIT 1) AS `nextOrder`,

                    (SELECT C_ID FROM categories WHERE C_Level = :C_Level AND C_ComponentID = :C_ComponentID AND `C_Order` = 
                    (SELECT `C_Order` FROM categories WHERE C_Level = :C_Level AND C_ComponentID = :C_ComponentID AND `C_Order` > 
                    (SELECT `C_Order` FROM categories WHERE C_ID = :C_ID) 
                    ORDER BY `C_Order` ASC LIMIT 1)) AS `nextCategoryID`";

            $parameters = array(':C_ID', ':C_Level', ':C_ComponentID');
            $values = array($this->getID(), $this->getLevel(), $this->getComponentID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT, PDO::PARAM_INT);


            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {
                $row = $result->fetch();

                // cập nhật order của next category bằng với currentOrder
                $sqlNextArticle = "update categories set `C_Order` = :C_Order where `C_ID` = :C_ID";
                $parameters2 = array(':C_Order', ':C_ID');
                $values2 = array($row->currentOrder, $row->nextCategoryID);
                $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlNextArticle, $parameters2, $values2, $types2);

                // cập nhật order của current category bằng với next order
                $sqlCurrentArticle = "update categories set `C_Order` = :C_Order where `C_ID` = :C_ID";
                $parameters3 = array(':C_Order', ':C_ID');
                $values3 = array($row->nextOrder, $this->getID());
                $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
            }
        }
    }

    function orderDown($db) {
        if ($db != null) {
            $sql = "SELECT (SELECT `C_Order` 
                    FROM categories 
                    WHERE C_ID = :C_ID) AS `currentOrder`,               
                
                    (SELECT `C_Order` FROM categories WHERE C_Level = :C_Level AND C_ComponentID = :C_ComponentID AND `C_Order` < 
                    (SELECT `C_Order` FROM categories WHERE C_ID = :C_ID) ORDER BY `C_Order` DESC LIMIT 1) 
                    AS `previousOrder`,

                    (SELECT C_ID FROM categories WHERE C_Level = :C_Level AND C_ComponentID = :C_ComponentID AND `C_Order` = 
                    (SELECT `C_Order` FROM categories WHERE C_Level = :C_Level AND C_ComponentID = :C_ComponentID AND `C_Order` < 
                    (SELECT `C_Order` FROM categories WHERE C_ID = :C_ID) ORDER BY `C_Order` DESC LIMIT 1) ) 
                    AS `previousCategoryID`";

            $parameters = array(':C_ID', ':C_Level', ':C_ComponentID');
            $values = array($this->getID(), $this->getLevel(), $this->getComponentID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT, PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {

                $row = $result->fetch();

                // if exist previousCategoryID and previousOrder
                if ($row->previousCategoryID != null && $row->previousOrder != null) {

                    // cập nhật order của previous article bằng với currentOrder
                    $sqlPreviousArticle = "update categories set `C_Order` = :C_Order where `C_ID` = :C_ID";
                    $parameters2 = array(':C_Order', ':C_ID');
                    $values2 = array($row->currentOrder, $row->previousCategoryID);
                    $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlPreviousArticle, $parameters2, $values2, $types2);


                    // cập nhật order của current article bằng với previous order
                    $sqlCurrentArticle = "update categories set `C_Order` = :C_Order where `C_ID` = :C_ID";
                    $parameters3 = array(':C_Order', ':C_ID');
                    $values3 = array($row->previousOrder, $this->getID());
                    $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
                }
            }
        }
    }

}

?>
